package com.es.hr.baoxiaoShenpi;

import java.lang.reflect.Field;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;

import com.es.common.CommonBean;
import com.es.common.CommonBiz;
import com.es.util.db.Dao;
import com.es.util.page.PageBean;
import com.es.util.page.PageNavigator;

public class BaoxiaoShenpiBiz {
	
	public boolean insertBaoxiaoShenpi(BaoxiaoShenpiBean bean)  throws Exception{
		// TODO Auto-generated method stub
		boolean result = false;
		String sql = "insert into hrm_baoxiao_shenpi ( ";
		String values = ") values(";
		String end = ")";
		Dao dao = new Dao();
		ArrayList<String> query = new ArrayList<String>();
		Field[] fds = Class.forName("com.es.hr.baoxiaoShenpi.BaoxiaoShenpiBean").getDeclaredFields(); 
		for(int i = 0; i < fds.length; ++i){
			if(fds[i].getName().equals("baoxiao_shenpi_id")) continue;
			Object o = fds[i].get(bean);
			if(o instanceof Float){
				query.add(new Float((Float) o).toString());
				sql += fds[i].getName() + ",";
				values += "?,";
			}
			else if(o instanceof Integer){
				query.add(new Integer((Integer) o).toString());
				sql += fds[i].getName() + ",";
				values += "?,";
			}else if(o == null){
			}else{
				query.add(new String((String) o).toString());
				sql += fds[i].getName() + ",";
				values += "?,";
			}
		}
		sql = sql + " del_flag " + values + "?" + end;
		query.add("N");
		try{
			dao.update(sql, query);
			result = true;
		}catch(Exception e){
			throw e;
		}finally{
			dao.close();
		}
		return result;
	}

	public PageBean searchBaoxiaoShenpi(String area, String province,
			String city, String rtm_code, String rtm_name, String ctm_code, String ctm_name, 
			String baoxiao_rtm_stateCode, String baoxiao_zongbu_stateCode, int curpage,boolean isRtm) throws Exception{
		// TODO Auto-generated method stub
		Dao dao = new Dao();
		PageBean pb = null;
		String sql ="";
		if(isRtm)
			sql= " where b.baoxiao_state_code = '0'  and b.del_flag = ? and  b.baoxiao_create_time >= ? and b.baoxiao_create_time <= ?  and a.area_code = p.area_code and p.province_code = c.province_code and c.city_code = b.city_code ";
		else
			sql= " where b.baoxiao_state_code = '3' and b.del_flag = ? and  b.baoxiao_create_time >= ? and b.baoxiao_create_time <= ?  and a.area_code = p.area_code and p.province_code = c.province_code and c.city_code = b.city_code ";
		String[] sqlp = {" b.ctm_code like '%'+UPPER(?)+'%' "," b.ctm_name like '%'+UPPER(?)+'%' ", " b.rtm_code like '%'+UPPER(?)+'%' ", " b.rtm_name like '%'+UPPER(?)+'%' ", " a.area_code = ? ", " p.province_code = ? ", " c.city_code = ? " , " baoxiao_rtm_statecode = ? ", " baoxiao_zongbu_statecode = ? "};
		String sqlChailv =  " and chailv_fee > ? ";
		String and = " and ";
		Date now = new Date();
		int year = now.getYear() + 1900;
		int mouth = now.getMonth() + 1;
		String smouth = new Integer(mouth).toString();
		if(smouth.length() < 2) smouth = "0" + smouth;
		
		CommonBean shenpiRiqi = new CommonBiz().getCommonByTypeAndCode("baoxiaoproperty", "baoxiaoshenpiriqi");
		CommonBean chailvyuzhi = new CommonBiz().getCommonByTypeAndCode("baoxiaoproperty", "chailvyuzhi");
		String day = shenpiRiqi == null?"03":shenpiRiqi.getCommonValue();
		if(day.length() < 2) day = "0" + day;
		String start = year + "-" + smouth + "-" + "01";
		String end = year + "-" + smouth + "-" + day;
		
		String chailv = chailvyuzhi == null?"700":chailvyuzhi.getCommonValue();
		
		ArrayList<String> sqlParam = new ArrayList<String>();
		ArrayList<String> sqlParam2 = new ArrayList<String>();
		sqlParam.add("N");
		sqlParam.add(start);
		sqlParam.add(end);
		
		sqlParam2.add(ctm_code);
		sqlParam2.add(ctm_name);
		sqlParam2.add(rtm_code);
		sqlParam2.add(rtm_name);
		sqlParam2.add(area);
		sqlParam2.add(province);
		sqlParam2.add(city);
		sqlParam2.add(baoxiao_rtm_stateCode);
		sqlParam2.add(baoxiao_zongbu_stateCode);
		
		for(int i = 0; i < sqlParam2.size(); ++i){
			String param = sqlParam2.get(i);
			if(param != null && param.length() > 0){
					sql += and + sqlp[i];
					sqlParam.add(param);
				}
			}
		
		StringBuffer sb = new StringBuffer();
		Field[] fds = Class.forName("com.es.hr.baoxiaoShenpi.BaoxiaoShenpiBean").getDeclaredFields();
		
		for(int i=0;i<fds.length;i++){   
				sb.append("b." + fds[i].getName()+",");
		}		
		
				
		//总部审批
		if(baoxiao_rtm_stateCode.length() == 0){
			sql = " select " + sb.toString().substring(0, sb.toString().length() - 1)+ " from hrm_baoxiao_shenpi b, hrm_area a, hrm_province p, hrm_city c " + sql;
			sql += sqlChailv;
			sqlParam.add(chailv);
		}else{
			sql = " select " + sb.toString().substring(0, sb.toString().length() - 1)+ " from hrm_baoxiao_shenpi b, hrm_area a, hrm_province p, hrm_city c" + sql;
		}
		
		try{
			PageNavigator pageNav = new PageNavigator();
			int totalC = dao.getAllRow(sql, sqlParam);
			pageNav.setRowTotal(totalC);
			pageNav.setCurpage(curpage);
			if(totalC % pageNav.getPageRow() == 0){
				pageNav.setPageTotal(totalC / pageNav.getPageRow());
			}else{
				pageNav.setPageTotal(totalC / pageNav.getPageRow() + 1);
			}
			pb = dao.queryPageList(sql, pageNav, sqlParam);
			
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return pb;
	}
	
	public boolean zongbuPizhun( String baoxiao_shenpi_id, String zongbu_pizhun_date, String zongbu_pizhun_state, String zongbu_pifu_yijian) throws Exception{
		Dao dao = new Dao();
		String sql = "update hrm_baoxiao_shenpi  set  baoxiao_state_code = ? , baoxiao_zongbushenpi_time = ? , baoxiao_zongbu_statecode = ?, zongbu_pifu_yijian = ?"
				+ " where baoxiao_shenpi_id = ? and del_flag = 'N'";
		
		ArrayList<String> param = new ArrayList<String>();
		
		String sql2 = "select chailv_fee, baoxiao_rtm_statecode from hrm_baoxiao_shenpi where baoxiao_shenpi_id = ?";
		ArrayList<String> query = new ArrayList<String>();
		query.add(baoxiao_shenpi_id);
		ResultSet rs = dao.Select(sql2, query);
		float chailv_fee = 0;
		//int baoxiao_rtm_statecode = 0;
		int zongbu_pizhun_state_int = new Integer(zongbu_pizhun_state).intValue();
		while(rs.next()){
			chailv_fee = rs.getFloat("chailv_fee");
			//baoxiao_rtm_statecode = rs.getInt("baoxiao_rtm_statecode");
		}
		
		CommonBean chailvyuzhi = new CommonBiz().getCommonByTypeAndCode("baoxiaoproperty", "chailvyuzhi");
		String chailvfee = chailvyuzhi == null?"700":chailvyuzhi.getCommonValue();
		//比yuzhi大，需要总部审批
		if(chailv_fee > new Float(chailvfee).floatValue()){
			//modified by csq 2011-09-18 总部审批状态位设置为与总部审批的结果一致就可
			/*switch(baoxiao_rtm_statecode){
				case 0:param.add("0");break;//未审批
				case 1:param.add("1");break;//审批通过
				case 2:param.add("2");break;//审批拒绝
				}
			 */			
			param.add(zongbu_pizhun_state);
		}else{
			//不需要总部审批
			return true;
		}
		if(zongbu_pizhun_state_int == 2) {param.remove(0); param.add("2");}
		
		param.add(zongbu_pizhun_date);
		param.add(zongbu_pizhun_state);
		param.add(zongbu_pifu_yijian);
		param.add(baoxiao_shenpi_id);
		dao.update(sql, param );
		return true;
		
	}
	
	public boolean rtmPizhun( String baoxiao_shenpi_id, String rtm_pizhun_date, String rtm_pizhun_state, String rtm_pifu_yijian) throws Exception{
		Dao dao = new Dao();
		String sql = "update hrm_baoxiao_shenpi  set baoxiao_state_code = ? , baoxiao_rtmshenpi_time = ? , baoxiao_rtm_statecode = ?, rtm_pifu_yijian = ?"
				+ " where baoxiao_shenpi_id = ? and del_flag = 'N'";
		
		ArrayList<String> param = new ArrayList<String>();
		
		String sql2 = "select chailv_fee, baoxiao_zongbu_statecode from hrm_baoxiao_shenpi where baoxiao_shenpi_id = ?";
		ArrayList<String> query = new ArrayList<String>();
		query.add(baoxiao_shenpi_id);
		ResultSet rs = dao.Select(sql2, query);
		float chailv_fee = 0;
		int baoxiao_zongbu_statecode = 0;
		int rtm_pizhun_state_int = new Integer(rtm_pizhun_state).intValue();
		while(rs.next()){
			chailv_fee = rs.getFloat("chailv_fee");
			baoxiao_zongbu_statecode = rs.getInt("baoxiao_zongbu_statecode");
		}
		
		CommonBean chailvyuzhi = new CommonBiz().getCommonByTypeAndCode("baoxiaoproperty", "chailvyuzhi");
		String chailvfee = chailvyuzhi == null?"700":chailvyuzhi.getCommonValue();

		//比yuzhi大，需要总部审批
		if(chailv_fee > new Float(chailvfee).floatValue()){
			//modified by csq 订单审批状态设置有问题，rtm审批完成后，才需要总部
			/*switch(baoxiao_zongbu_statecode){
				case 0:param.add("0");break;
				case 1:param.add("1");break;
				case 2:param.add("2");break;
			}*/
			if(rtm_pizhun_state_int==1)
				param.add("3");//等待总部审批
			else
				param.add(rtm_pizhun_state);
		}else{
			param.add("1");
		}
		if(rtm_pizhun_state_int == 2) {param.remove(0); param.add("2");}
		
		
		param.add(rtm_pizhun_date);
		param.add(rtm_pizhun_state);
		param.add(rtm_pifu_yijian);
		param.add(baoxiao_shenpi_id);
		dao.update(sql, param );
		return true;
		
	}

	public PageBean searchCTMBaoxiao(String ctm_code, String startTime,
			String endTime, String baoxiao_state, int curpage) throws Exception{
		// TODO Auto-generated method stub
		boolean flag = false;
		Dao dao = new Dao();
		PageBean pb = null;
		String sql = " where del_flag = 'N' ";
		String[] sqlp = {"baoxiao_create_time >= ? "," baoxiao_create_time <= ? ", " ctm_code like '%'+UPPER(?)+'%' ", " baoxiao_state_code = ? "};
		String[] sqlcases = {" case  when baoxiao_state_code ='2' and baoxiao_lastmodify_time <= ? and baoxiao_lastmodify_time >= ? then 'true' else 'false' end as editable ",  
						" case  when baoxiao_state_code ='0' and baoxiao_lastmodify_time <= ? and baoxiao_lastmodify_time >= ?  then '未审批' when baoxiao_state_code ='1'  then '审批通过' when baoxiao_state_code ='2'  then '审批拒绝' when baoxiao_state_code ='3'  then '等待总部审批' else '未审批' end as baoxiao_state_string "};
		
		String and = " and ";
		String guoqiSql = " and baoxiao_lastmodify_time < ? ";
		Date now = new Date();
		int year = now.getYear() + 1900;
		int mouth = now.getMonth() + 1;
		String smouth = new Integer(mouth).toString();
		if(smouth.length() < 2) smouth = "0" + smouth;
		
		CommonBean commonBean = new CommonBiz().getCommonByTypeAndCode("baoxiaoproperty", "baoxiaoshenpiriqi");
		String day = commonBean == null?"03":commonBean.getCommonValue();
		if(day.length() < 2) day = "" + day;
		String start = year + "-" + smouth + "-" + "01";
		String end = year + "-" + smouth + "-" + day;
		
		ArrayList<String> sqlParam = new ArrayList<String>();
		ArrayList<String> sqlParam2 = new ArrayList<String>();
		sqlParam.add(end);
		sqlParam.add(start);
		sqlParam.add(end);
		sqlParam.add(start);
		
		sqlParam2.add(startTime);
		sqlParam2.add(endTime);
		sqlParam2.add(ctm_code);
		sqlParam2.add(baoxiao_state);
		
		for(int i = 0; i < sqlParam2.size(); ++i){
			String param = sqlParam2.get(i);
			if(param != null && param.length() > 0){
					sql += and + sqlp[i];
					sqlParam.add(param);
				}
			}
		
		
		StringBuffer sb = new StringBuffer();
		Field[] fds = Class.forName("com.es.hr.baoxiaoShenpi.BaoxiaoShenpiBean").getDeclaredFields();
		
		for(int i=0;i<fds.length;i++){   
				sb.append( fds[i].getName()+",");
		}		
		
		sql = " select " + sb + sqlcases[0] + "," + sqlcases[1] + " from hrm_baoxiao_shenpi " + sql;
		//过期的报销
		if(flag == true){
			sql += guoqiSql;
			sqlParam.add(start);
		}
		try{
			PageNavigator pageNav = new PageNavigator();
			int totalC = dao.getAllRow(sql, sqlParam);
			pageNav.setRowTotal(totalC);
			pageNav.setCurpage(curpage);
			if(totalC % pageNav.getPageRow() == 0){
				pageNav.setPageTotal(totalC / pageNav.getPageRow());
			}else{
				pageNav.setPageTotal(totalC / pageNav.getPageRow() + 1);
			}
			pb = dao.queryPageList(sql, pageNav, sqlParam);
			
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return pb;
	}

	public BaoxiaoShenpiBean searchBaoxiaoShenpiById(String baoxiao_shenpi_id) throws Exception{
		// TODO Auto-generated method stub
		String sql = "select *"
				+ " from hrm_baoxiao_shenpi where baoxiao_shenpi_id = ? ";
		BaoxiaoShenpiBean bean = new BaoxiaoShenpiBean();
		Dao dao = new Dao();
		ArrayList<String> param = new ArrayList<String>();
		param.add(baoxiao_shenpi_id);
		try{
		ResultSet rs = dao.Select(sql, param );
		Field[] fds = Class.forName("com.es.hr.baoxiaoShenpi.BaoxiaoShenpiBean").getDeclaredFields();
		while(rs.next()){
			for(int i = 0; i < fds.length; ++i){
				System.out.println("");
				if( fds[i].getName().equals("baoxiao_create_index") || fds[i].getName().equals("baoxiao_state_code") || fds[i].getName().equals("baoxiao_rtm_statecode")
						|| fds[i].getName().equals("baoxiao_zongbu_statecode"))
					fds[i].set(bean, rs.getInt(fds[i].getName()));
				else if(fds[i].getName().equals("tongxun_fee") || fds[i].getName().equals("traffic_fee") ||
						fds[i].getName().equals("zhusu_fee") || fds[i].getName().equals("canbu_fee") ||
						fds[i].getName().equals("chailv_fee") || fds[i].getName().equals("za_fee") ||
						fds[i].getName().equals("kuaidi_fee") || fds[i].getName().equals("computer_fee") ||
						fds[i].getName().equals("lastmouth_fee") || fds[i].getName().equals("total_fee") )
					fds[i].set(bean, rs.getFloat(fds[i].getName()));
				else fds[i].set(bean, rs.getString(fds[i].getName()));
			}
		}
		}catch(Exception e){
			throw e;
		}finally{
			dao.close();
		}

		return bean;
	}
	
	public boolean updateBaoxiaoShenpi(BaoxiaoShenpiBean bean)  throws Exception{
		// TODO Auto-generated method stub
		String sql = "update hrm_baoxiao_shenpi set ";
		String sqlwhere = "where baoxiao_shenpi_id = ?";
		Dao dao = new Dao();
		ArrayList<String> query = new ArrayList<String>();
		Field[] fds = Class.forName("com.es.hr.baoxiaoShenpi.BaoxiaoShenpiBean").getDeclaredFields(); 
		for(int i = 0; i < fds.length; ++i){
			if(fds[i].getName().equals("baoxiao_shenpi_id")) continue;
			Object o = fds[i].get(bean);
			if(o instanceof Float){
				query.add(new Float((Float) o).toString());
				sql += fds[i].getName() + " = ?,";
			}
			else if(o instanceof Integer){
				query.add(new Integer((Integer) o).toString());
				sql += fds[i].getName() + "= ?,";
			
			}else if(o == null){
				query.add("");
				sql += fds[i].getName() + "= ?,";
			}else{
				query.add(new String((String) o).toString());
				sql += fds[i].getName() + "= ?,";
			}
		}
		sql = sql + " del_flag = 'N' "   + sqlwhere;
		query.add(bean.baoxiao_shenpi_id);
		try{
			dao.update(sql, query);
			return true;
		}catch(Exception e){
			throw e;
		}finally{
			dao.close();
		}
	}
	
	public PageBean searchBaoxiaoShenpiHistory(String area, String province,
			String city, String rtm_code, String rtm_name, String ctm_code, String ctm_name, 
			String baoxiao_state, String startTime, String endTime, boolean isZongbu, int curpage) throws Exception{
		// TODO Auto-generated method stub
		boolean flag = false;
		Dao dao = new Dao();
		PageBean pb = null;
		String sql = "where b.del_flag = 'N' and a.area_code = p.area_code and p.province_code = c.province_code and c.city_code = b.city_code ";
		String[] sqlp = {" b.ctm_code like '%'+UPPER(?)+'%' "," b.ctm_name like '%'+UPPER(?)+'%' ", " b.rtm_code like '%'+UPPER(?)+'%' ", " b.rtm_name like '%'+UPPER(?)+'%' ", " a.area_code = ? ", " p.province_code = ? ", " c.city_code = ? ", " baoxiao_create_time >= ? "," baoxiao_create_time <= ? ", " b.baoxiao_state_code = ? "};
		String sqlcases = 
		" case  when baoxiao_state_code ='0' and baoxiao_lastmodify_time <= ? and baoxiao_lastmodify_time >= ?  then '未审批' when baoxiao_state_code ='1'  then '审批通过' when baoxiao_state_code ='2' then '审批拒绝' when baoxiao_state_code ='3'  then '等待总部审批'   else '未审批' end as baoxiao_state_string ";

		String guoqiSql = " and baoxiao_lastmodify_time < ? ";
		String and = " and ";
		Date now = new Date();
		int year = now.getYear() + 1900;
		int mouth = now.getMonth() + 1;
		String smouth = new Integer(mouth).toString();
		if(smouth.length() < 2) smouth = "0" + smouth;

		CommonBean commonBean = new CommonBiz().getCommonByTypeAndCode("baoxiaoproperty", "baoxiaoshenpiriqi");
		String day = commonBean == null?"03":commonBean.getCommonValue();
		if(day.length() < 2) day = "" + day;
		String start = year + "-" + smouth + "-" + "01";
		String end = year + "-" + smouth + "-" + day;
		
		ArrayList<String> sqlParam = new ArrayList<String>();
		ArrayList<String> sqlParam2 = new ArrayList<String>();
		sqlParam.add(end);
		sqlParam.add(start);
		
		sqlParam2.add(ctm_code);
		sqlParam2.add(ctm_name);
		sqlParam2.add(rtm_code);
		sqlParam2.add(rtm_name);
		sqlParam2.add(area);
		sqlParam2.add(province);
		sqlParam2.add(city);
		sqlParam2.add(startTime);
		sqlParam2.add(endTime);
		sqlParam2.add(baoxiao_state);
		
		for(int i = 0; i < sqlParam2.size(); ++i){
			String param = sqlParam2.get(i);
			if(param != null && param.length() > 0){
					sql += and + sqlp[i];
					sqlParam.add(param);
				}
			}
		
		StringBuffer sb = new StringBuffer();
		Field[] fds = Class.forName("com.es.hr.baoxiaoShenpi.BaoxiaoShenpiBean").getDeclaredFields();
		
		for(int i=0;i<fds.length;i++){   
				sb.append("b." + fds[i].getName()+",");
		}		
		
				
		//总部查看
		if(isZongbu == true){
			sql = " select " + sb.toString()+ sqlcases  + " from hrm_baoxiao_shenpi b, hrm_area a, hrm_province p, hrm_city c " + sql;
			if(flag){
				sqlParam.add(start);
				sql += guoqiSql;
			}
		}else{
			sql = " select " + sb.toString() + sqlcases + " from hrm_baoxiao_shenpi b, hrm_area a, hrm_province p, hrm_city c " + sql;
			if(flag){
				sqlParam.add(start);
				sql += guoqiSql;
			}
		}
		
		try{
			PageNavigator pageNav = new PageNavigator();
			int totalC = dao.getAllRow(sql, sqlParam);
			pageNav.setRowTotal(totalC);
			pageNav.setCurpage(curpage);
			if(totalC % pageNav.getPageRow() == 0){
				pageNav.setPageTotal(totalC / pageNav.getPageRow());
			}else{
				pageNav.setPageTotal(totalC / pageNav.getPageRow() + 1);
			}
			pb = dao.queryPageList(sql, pageNav, sqlParam);
			
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return pb;
	}
	
	
	public ArrayList<HashMap<String,String>> searchBaoxiaoShenpiHistoryForExport(String area, String province,
			String city, String rtm_code, String rtm_name, String ctm_code, String ctm_name, 
			String baoxiao_state, String startTime, String endTime, boolean isZongbu) throws Exception{
		// TODO Auto-generated method stub
		ArrayList<HashMap<String,String>> list = new ArrayList<HashMap<String,String>>();
		boolean flag = false;
		Dao dao = new Dao();
		String sql = "where b.del_flag = 'N' and a.area_code = p.area_code and p.province_code = c.province_code and c.city_code = b.city_code ";
		String[] sqlp = {" b.ctm_code like '%'+UPPER(?)+'%' "," b.ctm_name like '%'+UPPER(?)+'%' ", " b.rtm_code like '%'+UPPER(?)+'%' ", " b.rtm_name like '%'+UPPER(?)+'%' ", " a.area_code = ? ", " p.province_code = ? ", " c.city_code = ? ", " baoxiao_create_time >= ? "," baoxiao_create_time <= ? ", " b.baoxiao_state_code = ? "};
		String sqlcases = 
		" case  when baoxiao_state_code ='0' and baoxiao_lastmodify_time <= ? and baoxiao_lastmodify_time >= ?  then '未审批' when baoxiao_state_code ='1'  then '审批通过' when baoxiao_state_code ='2'  then '审批拒绝' when baoxiao_state_code ='3'  then '等待总部审批' else '未审批' end as baoxiao_state_string ";

		String guoqiSql = " and baoxiao_lastmodify_time < ? ";
		String and = " and ";
		String sqlRtmArea = " and z.area_code = a.area_code and z.emp_code = b.rtm_code ";
		Date now = new Date();
		int year = now.getYear() + 1900;
		int mouth = now.getMonth() + 1;
		String smouth = new Integer(mouth).toString();
		if(smouth.length() < 2) smouth = "0" + smouth;

		CommonBean commonBean = new CommonBiz().getCommonByTypeAndCode("baoxiaoproperty", "baoxiaoshenpiriqi");
		String day = commonBean == null?"03":commonBean.getCommonValue();
		if(day.length() < 2) day = "" + day;
		String start = year + "-" + smouth + "-" + "01";
		String end = year + "-" + smouth + "-" + day;
		
		ArrayList<String> sqlParam = new ArrayList<String>();
		ArrayList<String> sqlParam2 = new ArrayList<String>();
		sqlParam.add(end);
		sqlParam.add(start);
		
		sqlParam2.add(ctm_code);
		sqlParam2.add(ctm_name);
		sqlParam2.add(rtm_code);
		sqlParam2.add(rtm_name);
		sqlParam2.add(area);
		sqlParam2.add(province);
		sqlParam2.add(city);
		sqlParam2.add(startTime);
		sqlParam2.add(endTime);
		sqlParam2.add(baoxiao_state);
		
		for(int i = 0; i < sqlParam2.size(); ++i){
			String param = sqlParam2.get(i);
			if(param != null && param.length() > 0){
					sql += and + sqlp[i];
					sqlParam.add(param);
				}
			}
		
		StringBuffer sb = new StringBuffer();
		Field[] fds = Class.forName("com.es.hr.baoxiaoShenpi.BaoxiaoShenpiBean").getDeclaredFields();
		
		for(int i=0;i<fds.length;i++){   
				sb.append("b." + fds[i].getName()+",");
		}		
		
				
		//总部查看
		if(isZongbu == true){
			sql = " select " + sb.toString()+ sqlcases  + " from hrm_baoxiao_shenpi b, hrm_area a, hrm_province p, hrm_city c " + sql;
			if(flag){
				sqlParam.add(start);
				sql += guoqiSql;
			}
		}else{
			sql = " select " + sb.toString() + sqlcases + " from hrm_baoxiao_shenpi b, hrm_area a, hrm_province p, hrm_city c, hrm_area_rtm z " + sql + sqlRtmArea;
			if(flag){
				sqlParam.add(start);
				sql += guoqiSql;
			}
		}
		try{
			ResultSet resultSet = dao.Select(sql,sqlParam);
			ResultSetMetaData rss = resultSet.getMetaData();
			
			while(resultSet.next()){
					HashMap<String,String> map = new HashMap<String,String>();
					for(int i = 0;i<rss.getColumnCount(); i++){ 	
						Object o = resultSet.getObject(rss.getColumnName(i+1));
						if(!rss.getColumnName(i+1).equals("baoxiao_shenpi_id")){
							if(rss.getColumnName(i+1).equals("baoxiao_create_time")){
								map.put(rss.getColumnName(i+1), resultSet.getObject(rss.getColumnName(i+1)).toString().substring(0, 7));
							}else
								map.put(rss.getColumnName(i+1), resultSet.getObject(rss.getColumnName(i+1)).toString());
							
						}
							
					}	
				  list.add(map);
			  }
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return list;
	}
}
